#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import os
os.system("/var/www/bakuden")
os.system("/home/bakuden/.pyenv/versions/app/lib/site-packages")

import MySQLdb

db_properties = {
                 'DB_SLAVE_HOST':'192.168.10.237',
                 'DB_NAME':'bakuden',
                 'DB_USER':'mcadmin',
                 'DB_PASS':'mcadmin',
                 }

# 获取数据库连接
def get_db_slave():
    try:
        conn = MySQLdb.connect(
                     charset='utf8', init_command='SET NAMES UTF8',
                     host=db_properties['DB_SLAVE_HOST'],
                     db=db_properties['DB_NAME'], user=db_properties['DB_USER'], passwd=db_properties['DB_PASS'])
        conn.cursorclass = MySQLdb.cursors.DictCursor
    except Exception as e:
        print e.message
        return None
    return conn


ranking_sql='''
INSERT INTO bakuden.team_convention_ranking (
    team_id,
    convention_id,
    sticker_id,
    count,
    ranking,
    rank_date
) SELECT
    team_id,
    convention_id,
    sticker_id,
    total_count,
    rank,
    date_format(NOW(), '%y-%m-%d')
FROM
    (
        SELECT
            sticker_log_tmp.team_id,
            sticker_log_tmp.convention_id,
            sticker_log_tmp.sticker_id,
            sticker_log_tmp.total_count,
            @rownum :=@rownum + 1,

        IF (
            @sticker_id = sticker_log_tmp.sticker_id ,@rank :=@rank + 1 ,@rank := 1
        ) AS rank,
        @sticker_id := sticker_log_tmp.sticker_id
    FROM
        (
            SELECT
                team_id,
                convention_id,
                sticker_id,
                sum(count) AS total_count
            FROM
                bakuden_log.sticker_log
            GROUP BY
                team_id,
                sticker_id
            ORDER BY
                sticker_id ASC,
                total_count DESC
        ) AS sticker_log_tmp,
        (
            SELECT
                @rownum := 0,
                @sticker_id := NULL ,@rank := 0
        ) a
    ) result
WHERE
    rank <= 1000
'''

delete_sql = '''
DELETE
FROM
    team_convention_ranking
WHERE
    rank_date = date_format(NOW(), '%y-%m-%d');
'''


if __name__ == "__main__":
    connection = get_db_slave()
    cur = connection.cursor()
     
    del_count = cur.execute(delete_sql)
    ins_count = cur.execute(ranking_sql)
    
    cur.close()
    connection.commit()
    connection.close()
    
    print '-----------------del_count',del_count
    print '-----------------ins-count',ins_count
